from flask import Blueprint, jsonify, render_template
from sqlalchemy import func
from python.extensions import db
from python.model.model import Attraction
import logging
from sqlalchemy import case

from python.extensions import db
from python.model.model import Attraction
# 配置日志
logging.basicConfig(level=logging.DEBUG)
logger = logging.getLogger(__name__)

menu1_bp = Blueprint('menu1', __name__, url_prefix='/menu')


def _get_avg_value(column):
    avg_value = db.session.query(func.avg(column)).scalar()
    return round(float(avg_value or 0), 2)


@menu1_bp.route('/avg_price_data')
def avg_price_data():
    """获取平均价格数据"""
    logger.info("数据库调用: 查询景点平均价格")
    return jsonify(avg_price=_get_avg_value(Attraction.price))

@menu1_bp.route('/avg_rating_data')
def avg_rating_data():
    """获取平均评分数据"""
    logger.info("数据库调用: 查询景点平均评分")
    return jsonify(avg_rating=_get_avg_value(Attraction.rating))


@menu1_bp.route('/high_grade_attractions')
def high_grade_attractions():
    """获取各省4A-5A景区数量"""
    logger.info("数据库调用: 查询各省4A-5A景区数量")
    results = db.session.query(
        Attraction.province,
        func.count(Attraction.id).label('count')
    ).filter(Attraction.star_level.in_(['4A', '5A'])) \
        .group_by(Attraction.province) \
        .all()

    provinces, counts = zip(*results) if results else ([], [])

    return jsonify({
        'provinces': provinces,
        'counts': counts
    })


@menu1_bp.route('/hot_sales_attractions')
def hot_sales_attractions():
    """获取热门景点销量数据"""
    logger.info("数据库调用: 查询热门景点销量TOP20")
    results = Attraction.query.order_by(Attraction.sales.desc()).limit(20).all()

    attractions, sales = zip(*[(a.name, a.sales) for a in results]) if results else ([], [])

    return jsonify({
        'attractions': attractions,
        'sales': sales
    })


@menu1_bp.route('/get_price_pie_chart_data')
def get_price_pie_chart_data():
    """获取门票价格区间分布数据"""
    logger.info("数据库调用: 查询门票价格区间分布")
    try:
        logger.info("获取门票价格区间分布数据")
        price_ranges = [
            {'min': 0, 'max': 50, 'label': '0-50'},
            {'min': 50, 'max': 100, 'label': '50-100'},
            {'min': 100, 'max': 150, 'label': '100-150'},
            {'min': 150, 'max': 200, 'label': '150-200'},
            {'min': 200, 'max': 300, 'label': '200-300'},
            {'min': 300, 'max': 500, 'label': '300-500'},
            {'min': 500, 'max': 1000, 'label': '500-1000'},
            {'min': 1000, 'max': None, 'label': '1000以上'}
        ]

        results = []
        for pr in price_ranges:
            if pr['max'] is not None:
                count = db.session.query(func.count(Attraction.id)).filter(
                    Attraction.price >= pr['min'],
                    Attraction.price < pr['max']
                ).scalar()
            else:
                count = db.session.query(func.count(Attraction.id)).filter(
                    Attraction.price >= pr['min']
                ).scalar()

            results.append({
                'name': pr['label'],
                'value': count
            })

        logger.info(f"门票价格区间分布数据查询结果: {results}")

        return jsonify({
            'code': 0,
            'data': results
        })

    except Exception as e:
        logger.error(f"获取门票价格区间分布数据错误: {str(e)}", exc_info=True)
        return jsonify({
            'code': 1,
            'msg': str(e)
        }), 500


@menu1_bp.route('/get_price_pie_chart')
def get_price_pie_chart():
    logger.info("访问门票价格区间分布图表页面")
    return render_template('menu/pie_price.html')